# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/9/10 14:47'

'''
预计付款计划表统计 --- 数据库操作层
'''

from common.mssql_helper import g_msql_inst
from common.utils import create_uuid
from  collections import defaultdict
from k3_data_report.business.config import bussiness_cal_fields

class Cgyjfk(object):

    @staticmethod
    def get_gys_list(month):
        # 获取相应的对比sql查询语句  获取采购订单信息
        sql = bussiness_cal_fields['k3_yjfk']['cgyjfk_sql'] % month
        res_list = g_msql_inst.fetch_all(sql)
        dic = defaultdict(dict)
        for (gongys_name,guige_xinhao, jiesuan_type , bibie, caigou_date, caigou_code,brand,wuliao_name, number,shui_rate) in res_list:
            gongys_identify = f"{gongys_name}#{guige_xinhao}"
            dic[gongys_identify]=[jiesuan_type , bibie, caigou_date,caigou_code,brand,wuliao_name, number,shui_rate]
        return  dic


    @staticmethod
    def get_cgyjfk_with_kdfh_list(month):
        # 款到发货 计算应付款, 从采购订单获取
        sql = bussiness_cal_fields['k3_yjfk']['cgyjfk_by_kdfh_bymonth'] % month
        res_list1 = g_msql_inst.fetch_all(sql)
        dic = defaultdict(dict)
        for (gongys_name, guige_xinhao,  hanshui_unit, jiashui_heji) in res_list1:
            gongys_identify = f"{gongys_name}#{guige_xinhao}"
            dic[gongys_identify]=[hanshui_unit, jiashui_heji]
        return  dic


    @staticmethod
    def get_cgyjfk_with_yuejie_list(month):
        # 计算应付款  月结（采购发票）
        sql = bussiness_cal_fields['k3_yjfk']['cgyjfk_with_yuejie_bymonth'] % month
        res_list1 = g_msql_inst.fetch_all(sql)
        dic = defaultdict(dict)
        for (ghdw, ggxh, ddhsdj, jshj) in res_list1:
            gongys_identify = f"{ghdw}#{ggxh}"
            dic[gongys_identify]=[ddhsdj, jshj]
        return  dic


    @staticmethod
    def get_cgyjfk_with_waigou_list(month):
        # 计算应付款  外购入库（货到付款）
        sql = bussiness_cal_fields['k3_yjfk']['cgyjfk_with_waigou_bymonth'] % month
        res_list1 = g_msql_inst.fetch_all(sql)
        dic = defaultdict(dict)
        for (gongys, guige_xinghao, hanshui_uprice, hanshui_jine) in res_list1:
            gongys_identify = f"{gongys}#{guige_xinghao}"
            dic[gongys_identify]=[hanshui_uprice, hanshui_jine]
        return  dic

    @staticmethod
    def insert_cgyjfk(value_tuple):
        try:
            db_insert_sql = bussiness_cal_fields['k3_yjfk']['cgyjfk_insert_sql']
            excuete_sql = db_insert_sql % value_tuple
            g_msql_inst.db_excuete(excuete_sql)
            return True
        except Exception as e:
            return False




